package org.cncirtus.data.client;

import java.io.*;

import org.cncitrus.data.db.ChinaExportConn;
import org.cncitrus.data.db.ChinaImportConn;
import org.cncitrus.data.db.WorldTradeConn;
import org.cncitrus.data.db.WorldYieldAreaConn;

import jxl.*;
import jxl.write.*;
import jxl.format.*;

class Excel {
	public static void main(String args[]) {
//		String[] year,code,county,money,total;
//		String[] year,country,spec,area,products;
		String[] year,code,ec,ic,total,money;
		try {
			
			Workbook workbook = null;

			try {
				workbook = Workbook.getWorkbook(new File("d:\\a.xls"));
			} catch (Exception e) {				
				throw new Exception("file to import not found!");
			}

			Sheet sheet = workbook.getSheet(7);
			Cell cell = null;

			int columnCount = sheet.getColumns();//获取列数
//			System.out.println(columnCount);
			int rowCount = sheet.getRows();//获取行数
//			System.out.println(rowCount);
			year = new String[rowCount];
			code = new String[rowCount];
			ec = new String[rowCount];
			ic = new String[rowCount];
			total = new String[rowCount];
			money = new String[rowCount];
			for (int i = 0; i < rowCount; i++) {
				for (int j = 0; j < columnCount; j++) {
					// 注意，这里的两个参数，第一个是表示列的，第二才表示行
					cell = sheet.getCell(j, i);
					// 要根据单元格的类型分别做处理，否则格式化过的内容可能会不正确
//					if (cell.getType() == CellType.NUMBER) {
//						System.out.print(((NumberCell) cell).getValue());
//					} else if (cell.getType() == CellType.DATE) {
//						System.out.print(((DateCell) cell).getDate());
//					} else {
//						System.out.print(cell.getContents());
//					}
					String content = cell.getContents();
					if(j==1&&content.startsWith("8")){
						content = content.replace("8", "08");
					}
					
					switch(j){
					case 0:
						year[i] = content;
						break;
					case 1:
						code[i] = content;
						break;
					case 2:
						ec[i] = content;
						break;
					case 3:
						ic[i] = content;
						break;
					case 4:
						total[i] = content;
						break;	
					case 5:
						money[i] = content;
						break;
					}
//					System.out.print(content);
					// System.out.print(cell.getContents());
//					System.out.print("\t");
				}
//				System.out.print("\n");
			}
//			System.out.println(year[0]+"\t"+code[0]+"\t"+ec[0]+"\t"+ic[0]+"\t"+total[0]+"\t"+money[0]);
//			System.out.println(year[10]+"\t"+code[10]+"\t"+ec[10]+"\t"+ic[10]+"\t"+total[10]+"\t"+money[10]);
			// 关闭它，否则会有内存泄露
			workbook.close();
			String resp="插入数据";
			String[] value1,value2,value3,value4,value5,value6;
			for(int i=0;i<year.length;i=i+1000){
				int length = 1000>year.length-i?year.length-i:1000;
				System.out.print("数组长度"+length);
				value1 = new String[length];
				value2 = new String[length];
				value3 = new String[length];
				value4 = new String[length];
				value5 = new String[length];
				value6 = new String[length];
				System.out.println("\t游标位置"+i);
				for(int j=i;j<i+1000&&j<year.length;j++){
					value1[j-i] = year[j];
					value2[j-i] = code[j];
					value3[j-i] = ec[j];
					value4[j-i] = ic[j];
					value5[j-i] = total[j];
					value6[j-i] = money[j];
				}
//				System.out.println(value1[0]+"\t"+value2[0]+"\t"+value3[0]+"\t"+value4[0]+"\t"+value5[0]+"\t"+value6[0]);
				resp = new WorldTradeConn().insert(value1, value2, value3, value4, value5, value6);
				System.out.println(resp);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	//resp = new ChinaImportConn().insert(year, code, county, money, total);
	//resp = new ChinaExportConn().insert(year, code, county, money, total);
	//resp = new WorldYieldAreaConn().insert(year, country, spec, area, products);
}